package com.feidee.data.report.dao.impl;

import com.feidee.data.report.constant.Constant;
import com.feidee.data.report.dao.IPublishReportDao;
import com.feidee.data.report.model.CustomReportModel;
import com.feidee.data.report.util.DbOperationUtil;
import com.feidee.data.report.util.ToolUtils;
import org.apache.log4j.Logger;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class CRPublishDaoImpl implements IPublishReportDao {

    private static Logger logger = Logger.getLogger(CRPublishDaoImpl.class);
    private DataSource confDataSource;
    private DataSource cusMysqlDataSource;

    public CRPublishDaoImpl() {
    }

    @SuppressWarnings("resource")
    public String crPublishReport(CustomReportModel crpm) {
        // 自定义报表发布
        // 将报表配置库对应的report_key的is_enable字段设置为1
        // 同时将
        Connection confConn = null;
        Connection reportConn = null;
        PreparedStatement updateStmt = null;
        ResultSet rs = null;
        String reportName = crpm.getApplysqlname();

        try {
            // 复制tmp表的内容到正式表，并且创建新表，删除tmp表
            confConn = confDataSource.getConnection();
            reportConn = cusMysqlDataSource.getConnection();
            //删除正式表
            String deleteTblStr = "DROP TABLE IF EXISTS `" + reportName + "`";
            updateStmt = reportConn.prepareStatement(deleteTblStr);
            updateStmt.execute();
            //删除视图
            dropViewRefFormal(crpm);
            String renameTblStr = "rename table `" + Constant.CUSTOM_REPORT_MYSQL_TABLE_PREFIX + reportName
                    + "` to `" + reportName + "`";
            updateStmt = reportConn.prepareStatement(renameTblStr);
            updateStmt.execute();
            logger.info("发布自定义报表 " + crpm.getId() + " => " + crpm.getApplysqlname()
                    + " 重命名表名,SQL语句: " + renameTblStr + "成功");

            // 将自定义报表的is_enable字段置为1
            String publishStr = "update report_sql set is_enable=1 where is_custom = 1 and report_key = '" +
                    reportName + "'";
            updateStmt = confConn.prepareStatement(publishStr);
            updateStmt.execute();
            logger.info("发布自定义报表 " + crpm.getId() + " => " + crpm.getApplysqlname()
                    + " 修改配置库is_enable字段SQL语句: " + publishStr + "成功");

            //修改Mysql语句
            int res = modifyMysqlTmpTbl(crpm);
            if (-1 == res) {
                return "3" + Constant.ERR_MSG_SPLIT_FLAG + "发布自定义报表修改Mysql语句失败[将Mysql中tmp表变为正式表]!";
            }
        } catch (SQLException e) {
            logger.info("发布自定义报表 " + crpm.getId() + " => " + crpm.getApplysqlname()
                    + " 更改配置库" + reportName + " 失败, ", e);
            return "3" + Constant.ERR_MSG_SPLIT_FLAG + "发布自定义报表更改配置库失败," + e.getMessage();
        } finally {
            DbOperationUtil.cleanConnEnv(reportConn, updateStmt, rs);
            DbOperationUtil.cleanConnEnv(confConn, updateStmt, rs);
        }

        return "0" + Constant.ERR_MSG_SPLIT_FLAG + "发布自定义报表rename临时表,更改配置库成功!";
    }

    /**
     * 修改配置库中Mysql语句中的tmp表到正式表
     *
     * @param crpm
     * @return
     */
    public int modifyMysqlTmpTbl(CustomReportModel crpm) {

        Connection confConn = null;
        PreparedStatement updateStmt = null;
        ResultSet rs = null;
        String reportName = crpm.getApplysqlname();
        String selMysqlStr = "select report_key,report_index,hqlcontext,sqlcontext from report_sql where is_custom = " +
                "1 and report_key = '"
                + reportName + "'";
        String updateMysqlStr = "update report_sql set hqlcontext=?,sqlcontext=? where report_key=? and report_index=?";
        try {
            // 更正报表的Mysql语句,修正里面操作的Mysql表名
            confConn = confDataSource.getConnection();
            updateStmt = confConn.prepareStatement(selMysqlStr);
            rs = updateStmt.executeQuery();
            Map<String, String> mysqlStrMap = new HashMap<String, String>();
            while (rs != null && rs.next()) {
                String report_key = rs.getString("report_key");
                String report_index = rs.getString("report_index");
                String sqlContext = rs.getString("sqlcontext");
                String hqlContext = rs.getString("hqlcontext");
                mysqlStrMap.put(report_key + Constant.ERR_MSG_SPLIT_FLAG + report_index + Constant.ERR_MSG_SPLIT_FLAG
                                + hqlContext.replace(Constant.CUSTOM_REPORT_MYSQL_TABLE_PREFIX + reportName,
                        reportName),
                        sqlContext.replace(Constant.CUSTOM_REPORT_MYSQL_TABLE_PREFIX + reportName, reportName));

            }
            confConn.setAutoCommit(false);
            updateStmt = confConn.prepareStatement(updateMysqlStr);
            for (Map.Entry<String, String> entry : mysqlStrMap.entrySet()) {
                String[] keys = entry.getKey().split(Constant.ERR_MSG_SPLIT_FLAG, -1);
                String mysqlStr = entry.getValue();
                updateStmt.setString(1, keys[2]);
                updateStmt.setString(2, mysqlStr);
                updateStmt.setString(3, keys[0]);
                updateStmt.setString(4, keys[1]);
                updateStmt.addBatch();
            }
            updateStmt.executeBatch();
            confConn.commit();
            updateDependencyReportPriority(reportName, Integer.valueOf(crpm.getPriority()));
            logger.info("发布自定义报表 " + crpm.getId() + " => " + crpm.getApplysqlname()
                    + " 修改Mysql语句:" + selMysqlStr + " 成功!!!");
            return 0;
        } catch (SQLException e) {
            logger.info("发布自定义报表 " + crpm.getId() + " => " + crpm.getApplysqlname()
                    + " 修改Mysql语句" + selMysqlStr + " 失败, ", e);
            return -1;
        } finally {
            DbOperationUtil.cleanConnEnv(confConn, updateStmt, rs);
        }

    }

    /**
     * 更改依赖报表优先级（提高优先级到当前报表的优先级，若比当前优先级更高不会修改）
     *
     * @param reportName
     * @param priority
     */
    private void updateDependencyReportPriority(String reportName, int priority) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        ResultSet previewResultSet = null;
        try {
            connection = confDataSource.getConnection();
            String selectSql = "SELECT DISTINCT report_dependency FROM report_sql WHERE report_key='" + reportName +
                    "'";
            statement = connection.createStatement();
            resultSet = statement.executeQuery(selectSql);
            if (resultSet.next()) {
                String updateKey = "";
                String reportDependency = resultSet.getString("report_dependency");
                String previewPrioritySql = "SELECT report_key,priority FROM report_sql WHERE report_key IN ('" +
                        reportDependency.replace(",", "','") + "')";
                previewResultSet = statement.executeQuery(previewPrioritySql);
                while (previewResultSet.next()) {
                    int dependentPriority = previewResultSet.getInt("priority");
                    if (dependentPriority < priority) {
                        String reportKey = previewResultSet.getString("report_key");
                        updateKey += "'" + reportKey + "',";
                    }
                }
                if (!updateKey.equals("")) {
                    String updateSql = String.format("UPDATE report_sql SET priority=%d WHERE report_key IN (%s)",
                            priority, updateKey.substring(0, updateKey.length() - 1));
                    statement.execute(updateSql);
                    logger.info("自定义报表 " + reportName
                            + " 更新依赖报表优先级成功，RUNSQL: " + updateSql);
                }
            }
        } catch (SQLException e) {
            logger.info("自定义报表 " + reportName
                    + " 更新依赖报表优先级失败", e);
        } finally {
            DbOperationUtil.cleanConnEnv(connection, statement, resultSet);
            DbOperationUtil.cleanConnEnv(previewResultSet);
        }
    }

    /**
     * 删除到正式表的视图
     *
     * @param crpm
     */
    public String dropViewRefFormal(CustomReportModel crpm) {
        Connection reportConn = null;
        PreparedStatement dropStmt = null;
        String dropViewSql = "DROP VIEW IF EXISTS `" + Constant.CUSTOM_VIEW_MYSQL_TABLE_PREFIX + crpm.getApplysqlname
                () + "`";
        try {
            reportConn = DbOperationUtil.getMysqlConntion(Constant.CUSTOM_REPORT_FLAG);
            dropStmt = reportConn.prepareStatement(dropViewSql);
            dropStmt.execute();
            logger.info("发布自定义报表 " + crpm.getId() + " => " + crpm.getApplysqlname()
                    + " 删除视图 SQL:" + dropViewSql + " 成功!");
        } catch (Exception e) {
            logger.error("发布自定义报表 " + crpm.getId() + " => " + crpm.getApplysqlname()
                    + " 删除视图失败,SQL:" + dropViewSql + ",", e);
            return "3" + Constant.ERR_MSG_SPLIT_FLAG + "发布自定义报表删除视图失败," + e.getMessage();
        } finally {
            DbOperationUtil.cleanConnEnv(reportConn, dropStmt);
        }

        return "0" + Constant.ERR_MSG_SPLIT_FLAG + "发布自定义报表删除视图成功";
    }

    /**
     * 更新表数据并删除tmp表
     *
     * @param crpm
     * @return
     */
    public String updateAndDropTmpTable(CustomReportModel crpm) {
        Connection reportConn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        PreparedStatement replaceRs = null;
        String querySql = "select * from `" + Constant.CUSTOM_REPORT_MYSQL_TABLE_PREFIX + crpm.getApplysqlname() + "`";
        String replaceSql = "REPLACE INTO `" + crpm.getApplysqlname() + "` (";
        String dropSql = "DROP TABLE IF EXISTS `" + Constant.CUSTOM_REPORT_MYSQL_TABLE_PREFIX + crpm.getApplysqlname
                () + "`";
        try {
            reportConn = DbOperationUtil.getMysqlConntion(Constant.CUSTOM_REPORT_FLAG);
            stmt = reportConn.prepareStatement(querySql);
            rs = stmt.executeQuery();
            //获取列名
            String[] colN = null;
            if (rs != null) {
                ResultSetMetaData rsm = rs.getMetaData();
                List<String> colList = new ArrayList<>();
                for (int i = 0; i < rsm.getColumnCount(); i++) {
                    String fieldName = rsm.getColumnLabel(i + 1);
                    if (!"create_time".equals(fieldName) && !"update_time".equals(fieldName)) {
                        colList.add(fieldName);
                    }
                }
                colN = colList.toArray(new String[colList.size()]);
            }
            //拼接REPLACE语句
            for (String col : colN) {
                replaceSql += "`" + col + "`,";
            }
            replaceSql = ToolUtils.trimComma(replaceSql);
            replaceSql += ") values (";
            for (int i = 0; i < colN.length; i++) {
                replaceSql += "?,";
            }
            replaceSql = ToolUtils.trimComma(replaceSql);
            replaceSql += ")";
            logger.info("发布自定义报表 " + crpm.getId() + " => " + crpm.getApplysqlname()
                    + " 实时SQL变化导致需要更新临时表到正式表,SQL:" + replaceSql);

            reportConn.setAutoCommit(false);
            replaceRs = reportConn.prepareStatement(replaceSql);
            int flag = 0;
            while (rs.next()) {
                for (int i = 0; i < colN.length; i++) {
                    replaceRs.setString(i + 1, rs.getString(colN[i]));
                }
                replaceRs.addBatch();
                flag++;

                if (flag > 200) {
                    replaceRs.executeBatch();
                    reportConn.commit();
                    flag = 0;
                }
            }
            if (0 != flag) {
                replaceRs.executeBatch();
                reportConn.commit();
            }
            //删除tmp表
            stmt = reportConn.prepareStatement(dropSql);
            stmt.execute();

            logger.info("发布自定义报表 " + crpm.getId() + " => " + crpm.getApplysqlname()
                    + " 成功, 更新临时表数据到正式表并删除临时表成功!");
        } catch (Exception e) {
            logger.error("发布自定义报表 " + crpm.getId() + " => " + crpm.getApplysqlname()
                    + " 失败,实时SQL改变导致需要更新临时表到正式表", e);
            return "3" + Constant.ERR_MSG_SPLIT_FLAG + "发布自定义报表删除tmp表并更新正式表数据成功," + e.getMessage();
        } finally {
            DbOperationUtil.cleanConnEnv(reportConn, stmt, rs);
            DbOperationUtil.cleanConnEnv(replaceRs);
        }

        return "0" + Constant.ERR_MSG_SPLIT_FLAG + "发布自定义报表删除tmp表并更新正式表数据成功";
    }

    /**
     * 删除正式表的数据
     *
     * @param crpm
     */
    public String delFormalTblData(CustomReportModel crpm) {
        Connection reportConn = null;
        PreparedStatement delStmt = null;
        String delTblSql = "DELETE FROM `" + crpm.getApplysqlname() + "`";
        try {
            reportConn = DbOperationUtil.getMysqlConntion(Constant.CUSTOM_REPORT_FLAG);
            delStmt = reportConn.prepareStatement(delTblSql);
            delStmt.execute();
            logger.info("发布自定义报表 " + crpm.getId() + " => " + crpm.getApplysqlname()
                    + " 清除原表数据:" + delTblSql + " 成功!");
        } catch (Exception e) {
            logger.error("发布自定义报表 " + crpm.getId() + " => " + crpm.getApplysqlname()
                    + " 清除原表数据失败,SQL:" + delTblSql + ",", e);
            return "3" + Constant.ERR_MSG_SPLIT_FLAG + "发布自定义报表清除原表数据失败," + e.getMessage();
        } finally {
            DbOperationUtil.cleanConnEnv(reportConn, delStmt);
        }

        return "0" + Constant.ERR_MSG_SPLIT_FLAG + "发布自定义报表清除原表数据成功";
    }

    /**
     * 下线报表
     *
     * @param report_key
     * @return
     */
    @Override
    public String offlineReport(String reportKey) {

        Connection confConn = null;
        PreparedStatement updateStmt = null;

        try {
            confConn = confDataSource.getConnection();

            // 将自定义报表的is_enable字段置为0
            String offlineStr = "update report_sql set is_enable=0 where report_key = '" + reportKey + "'";
            logger.info("下线报表 SQL:" + offlineStr);
            updateStmt = confConn.prepareStatement(offlineStr);
            updateStmt.execute();
            logger.info("下线报表 " + reportKey + " 成功");
        } catch (SQLException e) {
            logger.info("下线报表 " + reportKey + " 失败, ", e);
            return "-1" + Constant.ERR_MSG_SPLIT_FLAG + "下线报表" + reportKey + "失败," + e.getMessage();
        } finally {
            DbOperationUtil.cleanConnEnv(confConn, updateStmt);
        }

        return "0" + Constant.ERR_MSG_SPLIT_FLAG + "下线报表" + reportKey + "成功.";
    }

    public DataSource getConfDataSource() {
        return confDataSource;
    }

    public void setConfDataSource(DataSource confDataSource) {
        this.confDataSource = confDataSource;
    }

    public DataSource getCusMysqlDataSource() {
        return cusMysqlDataSource;
    }

    public void setCusMysqlDataSource(DataSource cusMysqlDataSource) {
        this.cusMysqlDataSource = cusMysqlDataSource;
    }

}
